package com.cars.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.cars.dao.util.ConectionManager;
import com.cars.dto.Product;
import com.cars.dto.ProductLine;

public class InventoryDao {

	public List<ProductLine> getAllProductLines() {

		ResultSet resultSet = null;

		Statement statement = null;
		List<ProductLine> productLinesList = new ArrayList<ProductLine>();
		ProductLine productLine;
		try {
			// This will load the MySQL driver, each DB has its own driver
			Class.forName("com.mysql.jdbc.Driver");
			// Setup the connection with the DB

			// Statements allow to issue SQL queries to the database
			statement = ConectionManager.getInstance().getConnection()
					.createStatement();
			// Result set get the result of the SQL query
			resultSet = statement.executeQuery("select * from productlines");
			if (resultSet == null) {
				return null;
			}

			while (resultSet.next()) {
				productLine = new ProductLine();
				productLine.setProductLine("productLine");
				productLine.setTextDescription("textDescription");
				productLine.setHtmlDescription("htmlDescription");
				productLinesList.add(productLine);
			}

		} catch (Exception exception) {
			exception.printStackTrace();

		} finally {
			try {

				if (resultSet != null) {
					resultSet.close();
				}

				if (statement != null) {
					statement.close();

				}
			} catch (SQLException e) {
				e.printStackTrace();
			}

		}

		return productLinesList;

	}

	public List<Product> searchAllCars() {

		ResultSet resultSet = null;

		Statement statement = null;
		List<Product> products = new ArrayList<Product>();
		Product product;
		try {
			// This will load the MySQL driver, each DB has its own driver
			Class.forName("com.mysql.jdbc.Driver");
			// Setup the connection with the DB

			// Statements allow to issue SQL queries to the database
			statement = ConectionManager.getInstance().getConnection()
					.createStatement();
			// Result set get the result of the SQL query
			resultSet = statement.executeQuery("select * from products");
			if (resultSet == null) {
				return null;
			}

			while (resultSet.next()) {
				product = new Product();
				product.setBuyPrice(resultSet.getDouble("buyPrice"));
				product.setMSRP(resultSet.getDouble("MSRP"));
				product.setProductCode(resultSet.getString("productCode"));
				product.setProductDescription(resultSet
						.getString("productDescription"));
				product.setProductLine(resultSet.getString("productLine"));
				product.setProductName(resultSet.getString("productName"));
				product.setProductScale(resultSet.getString("productScale"));
				product.setProductVendor(resultSet.getString("productVendor"));
				product.setQuantityInStock(resultSet.getInt("quantityInStock"));
				products.add(product);
			}

		} catch (Exception exception) {
			exception.printStackTrace();

		} finally {
			try {

				if (resultSet != null) {
					resultSet.close();
				}

				if (statement != null) {
					statement.close();

				}
			} catch (SQLException e) {
				e.printStackTrace();
			}

		}

		return products;

	}

	public List<Product> searchCarsByPriceRange(Double lowerAmount,
			Double upperAmount) {

		ResultSet resultSet = null;

		PreparedStatement statement = null;
		List<Product> products = new ArrayList<Product>();
		Product product;
		try {
			// This will load the MySQL driver, each DB has its own driver
			Class.forName("com.mysql.jdbc.Driver");
			// Setup the connection with the DB

			// Statements allow to issue SQL queries to the database
			statement = ConectionManager
					.getInstance()
					.getConnection()
					.prepareStatement(
							"SELECT * FROM products WHERE buyPrice  BETWEEN   ? AND  ?");

			statement.setDouble(1, lowerAmount);
			statement.setDouble(2, upperAmount);

			// Result set get the result of the SQL query
			resultSet = statement.executeQuery();
			if (resultSet == null) {
				return null;
			}

			while (resultSet.next()) {
				product = new Product();
				product.setBuyPrice(resultSet.getDouble("buyPrice"));
				product.setMSRP(resultSet.getDouble("MSRP"));
				product.setProductCode(resultSet.getString("productCode"));
				product.setProductDescription(resultSet
						.getString("productDescription"));
				product.setProductLine(resultSet.getString("productLine"));
				product.setProductName(resultSet.getString("productName"));
				product.setProductScale(resultSet.getString("productScale"));
				product.setProductVendor(resultSet.getString("productVendor"));
				product.setQuantityInStock(resultSet.getInt("quantityInStock"));
				products.add(product);
			}

		} catch (Exception exception) {
			exception.printStackTrace();

		} finally {
			try {

				if (resultSet != null) {
					resultSet.close();
				}

				if (statement != null) {
					statement.close();

				}
			} catch (SQLException e) {
				e.printStackTrace();
			}

		}

		return products;

	}

	public List<Product> searchProductsByProductLine(String productLine) {

		ResultSet resultSet = null;

		PreparedStatement statement = null;
		List<Product> products = new ArrayList<Product>();
		Product product;
		try {
			// This will load the MySQL driver, each DB has its own driver
			Class.forName("com.mysql.jdbc.Driver");
			// Setup the connection with the DB

			// Statements allow to issue SQL queries to the database
			statement = ConectionManager
					.getInstance()
					.getConnection()
					.prepareStatement(
							"SELECT * FROM products WHERE  productLine like ?");

			statement.setString(1, "%" + productLine + "%");

			// Result set get the result of the SQL query
			resultSet = statement.executeQuery();
			if (resultSet == null) {
				return null;
			}

			while (resultSet.next()) {
				product = new Product();
				product.setBuyPrice(resultSet.getDouble("buyPrice"));
				product.setMSRP(resultSet.getDouble("MSRP"));
				product.setProductCode(resultSet.getString("productCode"));
				product.setProductDescription(resultSet
						.getString("productDescription"));
				product.setProductLine(resultSet.getString("productLine"));
				product.setProductName(resultSet.getString("productName"));
				product.setProductScale(resultSet.getString("productScale"));
				product.setProductVendor(resultSet.getString("productVendor"));
				product.setQuantityInStock(resultSet.getInt("quantityInStock"));
				products.add(product);
			}

		} catch (Exception exception) {
			exception.printStackTrace();

		} finally {
			try {

				if (resultSet != null) {
					resultSet.close();
				}

				if (statement != null) {
					statement.close();

				}
			} catch (SQLException e) {
				e.printStackTrace();
			}

		}

		return products;

	}

}
